


alter              function dbo.f_msquantitygetpriorinfo(
  @moneyid integer,
  @subjectid integer,
  @nyear integer,
  @nmonth integer,
  @filid integer
)
returns 
  @temp_table table (
    nbalyear integer ,
    nbalmonth integer ,
    bmbala decimal(18,2),
    bysumcredit decimal(18,2),
    bysumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumcredit decimal(18,2),
    fbysumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsum decimal(18,2),
    bmqtybala decimal(18,2),
    byqtysumcredit decimal(18,2),
    byqtysumdebit decimal(18,2),
    tmqtysumcredit decimal(18,2),
    tmqtysumdebit decimal(18,2)
  )
   
as  

begin
  declare @nbalyear integer
  declare @nbalmonth integer
  declare @maxyear integer
  declare @maxmonth integer
  declare @newfilid integer
  declare @bmbala decimal(18,2) 
  declare @bysumcredit decimal(18,2)
  declare @bysumdebit decimal(18,2)
  declare @tmsumcredit decimal(18,2)
  declare @tmsumdebit decimal(18,2)
  declare @tmsum decimal(18,2)
  declare @fbmbala decimal(18,2)
  declare @fbysumcredit decimal(18,2)
  declare @fbysumdebit decimal(18,2)
  declare @ftmsumcredit decimal(18,2)
  declare @ftmsumdebit decimal(18,2)
  declare @ftmsum decimal(18,2)
  declare @bmqtybala decimal(18,2)
  declare @byqtysumcredit decimal(18,2)
  declare @byqtysumdebit decimal(18,2)
  declare @tmqtysumcredit decimal(18,2)
  declare @tmqtysumdebit decimal(18,2)
  declare @bolddata integer

  declare @sum_table table( 
    nbalyear integer,
    nbalmonth integer,
    bmbala decimal(18,2),
    bysumcredit decimal(18,2),
    bysumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumcredit decimal(18,2),
    fbysumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsum decimal(18,2),
    bmqtybala decimal(18,2),
    byqtysumcredit decimal(18,2),
    byqtysumdebit decimal(18,2),
    tmqtysumcredit decimal(18,2),
    tmqtysumdebit decimal(18,2),
    filid integer
  )

  declare cur_filiale cursor local forward_only static read_only
    for select fi_filid from td_filiale where fi_filid = @filid or @filid = -99

  open cur_filiale 
  fetch next from  cur_filiale into @newfilid
  while @@fetch_status = 0 
  begin   
    set @nbalyear       = 0
    set @nbalmonth      = 0
    set @bmbala         = 0.00
    set @bysumcredit    = 0.00
    set @bysumdebit     = 0.00
    set @tmsumcredit    = 0.00
    set @tmsumdebit     = 0.00
    set @tmsum          = 0.00
    set @fbmbala        = 0.00
    set @fbysumcredit   = 0.00
    set @fbysumdebit    = 0.00
    set @ftmsumcredit   = 0.00
    set @ftmsumdebit    = 0.00
    set @ftmsum         = 0.00
    set @bmqtybala      = 0.00
    set @byqtysumcredit = 0.00
    set @byqtysumdebit  = 0.00
    set @tmqtysumcredit = 0.00
    set @tmqtysumdebit  = 0.00

    select @bolddata=count(ms.subjectid) from tft_msquantity ms
      inner join tfd_subject s on s.subjectid = ms.subjectid
      where s.subnode = 1 and s.subjectid = @subjectid
      and (@moneyid is null or ms.moneyid = @moneyid)
      and ((ms.nyear < @nyear) or (ms.nyear = @nyear and ms.nmonth < @nmonth))
      and ms.filid = @newfilid and isnull(ms.odata,0)=0

    if(@bolddata > 0)
    begin
      select top 1 @nbalyear       = ms.nyear,
                   @nbalmonth      = ms.nmonth,
                   @bmbala         = ms.bmbala,
                   @bysumcredit    = ms.bysumcredit,
                   @bysumdebit     = ms.bysumdebit,
                   @tmsumcredit    = ms.tmsumcredit,
                   @tmsumdebit     = ms.tmsumdebit,
                   @tmsum          = ms.tmsum,
                   @fbmbala        = ms.fbmbala,
                   @fbysumcredit   = ms.fbysumcredit,
                   @fbysumdebit    = ms.fbysumdebit,
                   @ftmsumcredit   = ms.ftmsumcredit,
                   @ftmsumdebit    = ms.ftmsumdebit,
                   @ftmsum         = ms.ftmsum,
                   @bmqtybala      = ms.bmqtybala,
                   @byqtysumcredit = ms.byqtysumcredit,
                   @byqtysumdebit  = ms.byqtysumdebit,
                   @tmqtysumcredit = ms.tmqtysumcredit,
                   @tmqtysumdebit  = ms.tmqtysumdebit
      from tft_msquantity ms
      inner join tfd_subject s on s.subjectid = ms.subjectid
      where s.subnode = 1 and s.subjectid = @subjectid
      and (@moneyid is null or ms.moneyid = @moneyid)
      and ((ms.nyear < @nyear) or (ms.nyear = @nyear and ms.nmonth < @nmonth))
      and ms.filid = @newfilid and isnull(ms.odata,0)=0
      order by ms.nyear desc,ms.nmonth desc
    end
    else
    begin
      select top 1 @nbalyear       = ms.nyear,
                   @nbalmonth      = ms.nmonth,
                   @bmbala         = ms.bmbala,
                   @bysumcredit    = ms.bysumcredit,
                   @bysumdebit     = ms.bysumdebit,
                   @tmsumcredit    = ms.tmsumcredit,
                   @tmsumdebit     = ms.tmsumdebit,
                   @tmsum          = ms.tmsum,
                   @fbmbala        = ms.fbmbala,
                   @fbysumcredit   = ms.fbysumcredit,
                   @fbysumdebit    = ms.fbysumdebit,
                   @ftmsumcredit   = ms.ftmsumcredit,
                   @ftmsumdebit    = ms.ftmsumdebit,
                   @ftmsum         = ms.ftmsum,
                   @bmqtybala      = ms.bmqtybala,
                   @byqtysumcredit = ms.byqtysumcredit,
                   @byqtysumdebit  = ms.byqtysumdebit,
                   @tmqtysumcredit = ms.tmqtysumcredit,
                   @tmqtysumdebit  = ms.tmqtysumdebit
      from tft_msquantity ms
      inner join tfd_subject s on s.subjectid = ms.subjectid
      where s.subnode = 1 and s.subjectid = @subjectid
      and ((ms.nyear < @nyear) or (ms.nyear = @nyear and ms.nmonth < @nmonth))
      and ms.filid = @newfilid and isnull(ms.odata,0)=1
      order by ms.nyear desc,ms.nmonth desc
    end
    if (@bmbala         is null) set @bmbala         = 0.00
    if (@bysumcredit    is null) set @bysumcredit    = 0.00
    if (@bysumdebit     is null) set @bysumdebit     = 0.00
    if (@tmsumcredit    is null) set @tmsumcredit    = 0.00
    if (@tmsumdebit     is null) set @tmsumdebit     = 0.00
    if (@tmsum          is null) set @tmsum          = 0.00
    if (@fbmbala        is null) set @fbmbala        = 0.00
    if (@fbysumcredit   is null) set @fbysumcredit   = 0.00
    if (@fbysumdebit    is null) set @fbysumdebit    = 0.00
    if (@ftmsumcredit   is null) set @ftmsumcredit   = 0.00
    if (@ftmsumdebit    is null) set @ftmsumdebit    = 0.00
    if (@ftmsum         is null) set @ftmsum         = 0.00
    if (@bmqtybala      is null) set @bmqtybala      = 0.00
    if (@byqtysumcredit is null) set @byqtysumcredit = 0.00
    if (@byqtysumdebit  is null) set @byqtysumdebit  = 0.00
    if (@tmqtysumcredit is null) set @tmqtysumcredit = 0.00
    if (@tmqtysumdebit  is null) set @tmqtysumdebit  = 0.00

    if (@nbalyear <> @nyear)
    begin
      set @bysumcredit  = 0.00
      set @bysumdebit   = 0.00
      set @fbysumcredit = 0.00
      set @fbysumdebit  = 0.00
    end

    insert into @sum_table values(@nbalyear, @nbalmonth, @bmbala, @bysumcredit,
                                 @bysumdebit, @tmsumcredit, @tmsumdebit, @tmsum,
                                 @fbmbala, @fbysumcredit, @fbysumdebit, @ftmsumcredit,
                                 @ftmsumdebit, @ftmsum, @bmqtybala, @byqtysumcredit,
                                 @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit, @newfilid)
    
    update @sum_table set bysumcredit = 0.00, bysumdebit = 0.00,
                          fbysumcredit = 0.00, fbysumdebit = 0.00 
      where nbalyear <> @nyear and filid = @newfilid
    fetch next from cur_filiale into  @newfilid
  end
  close cur_filiale
  deallocate  cur_filiale

  select @maxyear  = max(nbalyear)  from @sum_table
  select @maxmonth = max(nbalmonth) from @sum_table where nbalyear = @maxyear

  declare cur_tmp  cursor local forward_only static read_only
    for select nbalyear, nbalmonth, sum(bmbala), sum(bysumcredit), sum(bysumdebit),
      sum(tmsumcredit), sum(tmsumdebit), sum(tmsum), sum(fbmbala), sum(fbysumcredit),
      sum(fbysumdebit), sum(ftmsumcredit), sum(ftmsumdebit), sum(ftmsum),
      sum(bmqtybala), sum(byqtysumcredit), sum(byqtysumdebit), sum(tmqtysumcredit), sum(tmqtysumdebit)
    from @sum_table group by nbalyear, nbalmonth order by nbalyear desc, nbalmonth desc
  open cur_tmp 
  fetch next from  cur_tmp into @nbalyear, @nbalmonth, @bmbala,  
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,  
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum,
                                  @bmqtybala, @byqtysumcredit, @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit
  while @@fetch_status = 0 
  begin   
    if (@nbalyear = @maxyear and @nbalmonth = @maxmonth)  
    begin
      insert into @temp_table values(@nbalyear, @nbalmonth, @bmbala, @bysumcredit,
                                 @bysumdebit, @tmsumcredit, @tmsumdebit, @tmsum,
                                 @fbmbala, @fbysumcredit, @fbysumdebit, @ftmsumcredit,
                                 @ftmsumdebit, @ftmsum, @bmqtybala, @byqtysumcredit,
                                 @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit)
    end
    if (@nbalyear <> @maxyear or @nbalmonth <> @maxmonth)
    begin
        update @temp_table set bmbala = bmbala + @bmbala + @tmsumdebit - @tmsumcredit,
                               fbmbala = fbmbala + @fbmbala + @ftmsumdebit - @ftmsumcredit
        where nbalyear = @maxyear and nbalmonth = @maxmonth
    end
    fetch next from cur_tmp into @nbalyear, @nbalmonth, @bmbala,
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum,
                                  @bmqtybala, @byqtysumcredit, @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit
  end
  close cur_tmp
  deallocate  cur_tmp
  return

end





